package com.fred.taobao.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.fred.taobao.util.SqlUtil;

public class UpdateDatabaseDao {
	
	
	public void insertItem(String numIid,String itemName,String category ,float price){
		Connection conn=SqlUtil.getConnection();
		PreparedStatement pre=null;
		ResultSet result=null;
		try{
			pre=conn.prepareStatement("select num_iid from item_table where num_iid=?");
			pre.setString(1, numIid);
			result=pre.executeQuery();
			if(result.next()){
				return;
			}else{
				pre=conn.prepareStatement("insert into item_table values(?,?,?,?)");
				pre.setString(1, numIid);
				pre.setString(2,itemName);
				pre.setString(3, category);
				pre.setFloat(4,price);
				pre.execute();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(result!=null){
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pre!=null){
				try {
					pre.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public void insertSku(String skuId,String itemId,float price,String skuName) {
		Connection conn=SqlUtil.getConnection();
		PreparedStatement pre=null;
		ResultSet result=null;
		try{
			pre=conn.prepareStatement("select sku_id from sku_table where sku_id=?");
			pre.setString(1, skuId);
			result=pre.executeQuery();
			if(result.next()){
				return;
			}else{
				pre=conn.prepareStatement("insert into sku_table values(?,?,?,?)");
				pre.setString(1, skuId);
				pre.setString(2,itemId);
				pre.setFloat(3, price);
				pre.setString(4, skuName);
				pre.execute();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(result!=null){
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pre!=null){
				try {
					pre.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	public void insertSell(String orderId,String skuId,String itemIid,String orderNum,String orderDate, String deliverStatus,String totalFee, String buyerNick){
		Connection conn=SqlUtil.getConnection();
		PreparedStatement pre=null;
		ResultSet result=null;
		try{
			pre=conn.prepareStatement("select order_id from sell_table where order_id=?");
			pre.setString(1, orderId);
			result=pre.executeQuery();
			if(result.next()){
				return;
			}else{
				pre=conn.prepareStatement("insert into sell_table values(?,?,?,?,?,?,?,?)");
				pre.setString(1, orderId);
				pre.setString(2, skuId);
				pre.setString(3, itemIid);
				pre.setString(4, orderDate);
				pre.setInt(5,Integer.valueOf(orderNum));
				pre.setString(6, deliverStatus);
				pre.setFloat(7, Float.valueOf(totalFee));
				pre.setString(8, buyerNick);
				pre.execute();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(result!=null){
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pre!=null){
				try {
					pre.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public void clearData() {
		Connection conn=SqlUtil.getConnection();
		PreparedStatement pre=null;
		try{
			pre=conn.prepareStatement("delete from sell_table");
			pre.execute();
			pre=conn.prepareStatement("delete from item_table");
			pre.execute();
			pre=conn.prepareStatement("delete from sku_table");
			pre.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			
			if(pre!=null){
				try {
					pre.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}
